package com.muc.service.impl;

import com.muc.entity.CarDO;
import com.muc.req.CarRequest;
import com.muc.res.TableDTO;
import com.muc.service.CarService;
import com.muc.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

//封装的对数据库进行操作的类,通过服务器调用
public class CarServiceImpl implements CarService {

    //查询汽车
    @Override
    public TableDTO retrieveCars(CarRequest carRequest) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from car ");
//        sql.append("select * from ( select * from where ");
        if (carRequest.getSearchKey() != null && !"".equals(carRequest.getSearchKey().trim())) {//关键词不为空
            //用车辆ID查询  或者用车辆类别 车牌号  ---》 看情况
            sql.append("where car_id = '" + carRequest.getSearchKey().trim() + "'");//trim()去掉空格
            sql.append(" or car_type like '%" + carRequest.getSearchKey().trim() + "%'");//用车辆类别模糊查询

            //反复修改sql语句-----
        }
        //limit 可能要改
//        sql.append(" order by car_id desc , limit ").append(carRequest.getStart()).append(",")
//        sql.append("order by car_id desc ) car_id  limit ").append(carRequest.getStart()).append(",")
//        sql.append(" limit ").append(carRequest.getStart()).append(",")
//                .append(carRequest.getPageSize());

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();

            //查询记录
            returnDTO.setData(fillData(rs));
            //查询条数
            sql.setLength(0);
            sql.append("select count(*) from car ");
            if (carRequest.getSearchKey() != null && !"".equals(carRequest.getSearchKey().trim())) {//关键词不为空
                //用车辆ID查询  或者用车辆类别 车牌号  ---》 看情况
                sql.append("where car_id = " + carRequest.getSearchKey().trim());//trim()去掉空格
                sql.append(" or car_type like '%" + carRequest.getSearchKey().trim() + "%'");//用车辆类别模糊查询
            }

            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                returnDTO.setTotalCount(rs.getInt(1));//查询的结果列数下标1从1开始
            }

            //return returnDTO;//如果放在这里，会出现空指针异常，返回null,应该是finally的问题
        } catch (Exception e) {
            System.out.println(new RuntimeException("运行时内容为空"));
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
//            DBUtil.closeConn(conn);jsjjsjs
        }
        return returnDTO;
//        return null;
    }

    @Override
    public boolean add(CarDO carDO) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into car(car_id, car_type, car_num, car_status, car_date, car_price, car_info) ");
        sql.append("values(?,?,?,?,?,?,?) ");

        boolean addFlag = false;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, carDO.getCarId());
            ps.setString(2, carDO.getCarType());
            ps.setString(3, carDO.getCarNum());
            ps.setString(4, carDO.getCarStatus());
            ps.setString(5, carDO.getCarDate());
            ps.setDouble(6, carDO.getCarPrice());
            ps.setString(7, carDO.getCarInfo());
            addFlag = (ps.executeUpdate() == 1);

        } catch (Exception e) {
            System.out.println(new RuntimeException("添加车辆信息时错误"));
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
        }
        return addFlag;
    }

    @Override
    public CarDO getById(int selectedCarId) {
        StringBuilder sql = new StringBuilder("select * from car where car_id = ?");
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        CarDO carDO = new CarDO();
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, selectedCarId);
            rs = ps.executeQuery();
            while (rs.next()) {
                carDO.setCarId(rs.getInt("car_id"));
                carDO.setCarType(rs.getString("car_type"));
                carDO.setCarNum(rs.getString("car_num"));
                carDO.setCarStatus(rs.getString("car_status"));
                carDO.setCarDate(rs.getString("car_date"));
                carDO.setCarPrice(rs.getDouble("car_price"));
                carDO.setCarInfo(rs.getString("car_info"));
            }
        } catch (Exception e) {
            System.out.println(new RuntimeException("获取选中车辆信息时错误"));
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
        }
        return carDO;
    }

    @Override
    public boolean update(CarDO carDO) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update car set car_id = ? , car_type=? , car_num=?, car_status=?, car_date=?, car_price=?, car_info=?  ");
        sql.append(" where car_id = ? ");

        boolean updateFlag = false;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql.toString());
            //指定问号的值
            ps.setInt(1, carDO.getCarId());
            ps.setString(2, carDO.getCarType());
            ps.setString(3, carDO.getCarNum());
            ps.setString(4, carDO.getCarStatus());
            ps.setString(5, carDO.getCarDate());
            ps.setDouble(6, carDO.getCarPrice());
            ps.setString(7, carDO.getCarInfo());
            ps.setInt(8, carDO.getCarId());
            updateFlag = (ps.executeUpdate() == 1);

        } catch (Exception e) {
            System.out.println(new RuntimeException("更新车辆信息时错误"));
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
        }
        return updateFlag;
    }

    @Override
    public boolean delete(int[] selectedCarIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from car where car_id in ( ");
        int len_sel = selectedCarIds.length;
        for (int i = 0; i < len_sel; i++) {
            if (i == (len_sel - 1)) {
                sql.append("  ? )");
            } else {
                sql.append(" ?, ");
            }
        }
        boolean delFlag = false;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql.toString());
            for (int j = 0; j < len_sel; j++) {
                //設置參數， 從1開始
                ps.setInt(j + 1, selectedCarIds[j]);
            }

            delFlag = (ps.executeUpdate() == len_sel);
        } catch (Exception e) {
            System.out.println(new RuntimeException("刪除车辆信息时错误"));
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
        }
        return delFlag;
    }

    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            //处理查询出的每一条记录
            Vector<Object> oneRecord = new Vector<>();
            int carId = rs.getInt("car_id");
            String carType = rs.getString("car_type");
            String carNum = rs.getString("car_num");
            String carStatus = rs.getString("car_status");
            String carDate = rs.getString("car_date");
            double carPrice = rs.getDouble("car_price");
            String carInfo = rs.getString("car_info");
            oneRecord.addElement(carId);
            oneRecord.addElement(carType);
            oneRecord.addElement(carNum);
            oneRecord.addElement(carStatus);
            oneRecord.addElement(carDate);
            oneRecord.addElement(carPrice);
            oneRecord.addElement(carInfo);
            data.addElement(oneRecord);
        }
        return data;
    }
}
